﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace FirmaTransportowa
{
    public class Package : IModel<Package>
    {
        [System.ComponentModel.DisplayName("ID Paczki")]
        public int id { get; set; }
        [System.ComponentModel.DisplayName("Waga")]
        public float weight { get; set; }
        [System.ComponentModel.DisplayName("Status")]
        public PackageStatus status { get; set; }
        [System.ComponentModel.DisplayName("Data utworzenia")]
        public DateTime createDate { get; set; }

        public Sender sender;
        public Recipient recipient;
        public Transport firstTransport { get; set; }
        public Transport secondTransport { get; set; }

        public Package(int id, float weight, string status, DateTime createDate, int sender_id, int recipient_id, int first_transport_id, int second_transport_id)
        {
            this.id = id;
            this.weight = weight;
            this.status = EnumUtils.getPackageStatusByDescription(status);
            this.createDate = createDate;
            this.sender = Sender.getById(sender_id);
            this.recipient = Recipient.getById(recipient_id);
            this.firstTransport = Transport.getById(first_transport_id);
            this.secondTransport = Transport.getById(second_transport_id);
        }

        public Package(int id, float weight, string status, DateTime createDate, int sender_id, int recipient_id, int first_transport_id)
        {
            this.id = id;
            this.weight = weight;
            this.status = EnumUtils.getPackageStatusByDescription(status);
            this.createDate = createDate;
            this.sender = Sender.getById(sender_id);
            this.recipient = Recipient.getById(recipient_id);
            this.firstTransport = Transport.getById(first_transport_id);
            this.secondTransport = new Transport();
        }

        public Package(int id, float weight, string status, DateTime createDate, int sender_id, int recipient_id)
        {
            this.id = id;
            this.weight = weight;
            this.status = EnumUtils.getPackageStatusByDescription(status);
            this.createDate = createDate;
            this.sender = Sender.getById(sender_id);
            this.recipient = Recipient.getById(recipient_id);
            this.firstTransport = new Transport();
            this.secondTransport = new Transport();
        }

        public Package() { 
        }

        public int ReturnValue
        {
            get
            {
                return this.id;
            }
        }


        public int save()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            int createdId = -1;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                bool newObject = false;

                if (getById(this.id) == null)
                {
                    cmd.CommandText = "INSERT INTO Package(weight,status,add_date,sender_ID,recipient_ID" +
                                      (this.firstTransport != null && this.firstTransport.id != 0  ? ",first_transport_ID" : "") +
                                      (this.secondTransport != null && this.secondTransport.id != 0 ? ",second_transport_ID" : "") + ") VALUES(@weight,@status,@add_date,@sender_ID,@recipient_ID" +
                                      (this.firstTransport != null && this.firstTransport.id != 0 ? ",@first_transport_ID" : "") +
                                      (this.secondTransport != null && this.secondTransport.id != 0 ? ",@second_transport_ID" : "") + ") SELECT CONVERT(int, SCOPE_IDENTITY())";
                    newObject = true;
                   
                }
                else
                {
                    cmd.CommandText = "UPDATE Package SET weight=@weight, status=@status, add_date=@add_date, sender_ID=@sender_ID, recipient_ID=@recipient_ID" +
                        (this.firstTransport.id != 0 && this.firstTransport != null ? ", first_transport_ID=@first_transport_ID" : "") +
                        (this.secondTransport.id != 0 && this.secondTransport != null ? ", second_transport_ID=@second_transport_ID" : "") + " WHERE package_ID=@id";
                    cmd.Parameters.AddWithValue("@id", this.id);
                }

                cmd.Parameters.AddWithValue("@weight", this.weight);
                cmd.Parameters.AddWithValue("@status", EnumUtils.getEnumDescription(this.status));
                cmd.Parameters.AddWithValue("@add_date", this.createDate);
                cmd.Parameters.AddWithValue("@sender_ID", this.sender.id);
                cmd.Parameters.AddWithValue("@recipient_ID", this.recipient.id);
                if (this.firstTransport != null && this.firstTransport.id != 0) cmd.Parameters.AddWithValue("@first_transport_ID", this.firstTransport.id);
                if (this.secondTransport != null && this.secondTransport.id != 0) cmd.Parameters.AddWithValue("@second_transport_ID", this.secondTransport.id);
                cmd.Connection = conn;

                if (newObject)
                {
                    createdId = (int)cmd.ExecuteScalar();
                }
                else
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                System.Console.Write(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return createdId;
        }

        public IList<Package> list()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            IList<Package> list = new List<Package>();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select * from Package", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Package package = getById(int.Parse(reader["package_ID"].ToString()));

                        list.Add(package);
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }


        public static Package getById(int id)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                if (id < 0)
                {
                    return null;
                }
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Package WHERE package_ID=@id";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Connection = conn;
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    Package p = new Package();
                    p.id = int.Parse(reader["package_ID"].ToString());
                    p.status = EnumUtils.getPackageStatusByDescription(reader["status"].ToString());
                    p.weight = float.Parse(reader["weight"].ToString());
                    p.createDate = DateTime.Parse(reader["add_date"].ToString());
                    p.recipient = Recipient.getById(int.Parse(reader["recipient_ID"].ToString()));
                    p.sender = Sender.getById(int.Parse(reader["sender_ID"].ToString()));
                    p.firstTransport = reader["first_transport_ID"].ToString() != "" ? Transport.getById(int.Parse(reader["first_transport_ID"].ToString())) : new Transport();
                    p.secondTransport = reader["second_transport_ID"].ToString() != "" ? Transport.getById(int.Parse(reader["second_transport_ID"].ToString())) : new Transport();
                    conn.Close();
                    return p;
                }
            }
            catch (Exception e)
            {
                System.Console.Write(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return null;
        }

        public static BindingSource getPackagesForActualTransport(Transport transport) {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand();

                if (transport.destination_city == "sortownia")
                {
                    command = new SqlCommand("SELECT p.package_ID, s.name, s.surname, s.postcode, s.city, s.street, s.house_number, s.phone, p.weight, p.add_date, p.status FROM Sender s JOIN Package p ON s.sender_ID = p.sender_ID WHERE p.first_transport_ID = '" + transport.id + "' AND p.status = '" + EnumUtils.getEnumDescription(PackageStatus.NEW) + "'", connection);
                }
                else 
                {
                    command = new SqlCommand("SELECT p.package_ID, r.name, r.surname, r.postcode, r.city, r.street, r.house_number, r.phone, p.weight, p.add_date, p.status FROM Recipient r JOIN Package p ON r.recipient_ID = p.recipient_ID WHERE p.second_transport_ID = '" + transport.id + "' AND p.status = '" + EnumUtils.getEnumDescription(PackageStatus.DELIVERED) + "'", connection);
                }

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }


        public static List<Package> getPackagesByTransport(Transport transport) 
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            List<Package> list = new List<Package>();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select * from Package WHERE first_transport_ID = '" + transport.id + "' OR second_transport_ID = '" + transport.id + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Package package = getById(int.Parse(reader["package_ID"].ToString()));

                        list.Add(package);
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public void delete()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Package WHERE package_ID=@id";
                cmd.Parameters.AddWithValue("@id", this.id);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }
        }

        public static BindingSource getPackagesForDispatcher() 
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();
            try
            {
                connection.Open();

                SqlCommand command = new SqlCommand("SELECT p.package_ID, p.status, p.add_date, p.weight, s.city, r.city FROM (Package as p JOIN Sender as s ON s.sender_ID = p.sender_ID) JOIN Recipient as r ON r.recipient_ID = p.recipient_ID WHERE (p.status = '" + EnumUtils.getEnumDescription(PackageStatus.NEW) + "' AND p.first_transport_ID IS NULL) OR (p.status = '" + EnumUtils.getEnumDescription(PackageStatus.SORTED) + "' AND p.second_transport_ID IS NULL)", connection);

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }

        public static BindingSource getPackagesForCashier()
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();
            try
            {
                connection.Open();

                SqlCommand command = new SqlCommand("SELECT p.package_ID, p.status, p.add_date, p.weight, s.city, r.city FROM (Package as p JOIN Sender as s ON s.sender_ID = p.sender_ID) JOIN Recipient as r ON r.recipient_ID = p.recipient_ID", connection);

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }
    }
}
